/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Answer;
import dto.Exam;
import dto.Question;
import java.sql.*;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import util.DBUtil;

/**
 *
 * @author NHAN
 */
public class AnswerDAO {

    Connection conn;
    String sqlSelectByID = "select * from Answer where id=?";    
    String sqlCreate = "insert into [Answer](question_id, content, is_true) values(?, ?, ?)";
    String sqlUpdate = "update Answer set question_id=?, content= ?, is_true=?";
    String sqlDelete = "delete from Answer where id=?";
    Logger logger = Logger.getLogger(AnswerDAO.class);

    public AnswerDAO() {
    }

    /**
     * get a answer by specifying identifier value
     *
     * @param id
     * @return Answer object
     */
    public Answer getAnswer(int id) {
        Answer a = new Answer();
        try {
            conn = DBUtil.getConnection();

            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }//question_id, content, is_true
            PreparedStatement ps = conn.prepareStatement(sqlSelectByID);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                a.setQuestionId(rs.getInt("question_id"));
                a.setContent(rs.getString("content"));
                a.setIsTrue(rs.getBoolean("is_true"));
            }
            DBUtil.disconnect();

        } catch (SQLException ex) {
            logger.info("SQL error: " + ex.getMessage());
        } catch (Exception ex) {
            logger.info("Error: " + ex.getMessage());
        }
        return a;
    }
public ArrayList<Answer> getAnswers(Exam e){
    ArrayList<Answer> list = new ArrayList<Answer>();
        conn = DBUtil.getConnection();
        if (conn == null) {
            logger.info("Cannot get connection");
            return null;
        }
        try {
            int size=e.getQuestionSet().size();
            ArrayList<Question> qList=e.getQuestionSet();
            String sqlCommand="select * from Answer where question_id in (";
            for(int i=0;i<size;i++){
                if(i==0) sqlCommand+=qList.get(i).getId();
                else sqlCommand+=", "+qList.get(i).getId();
            }
            sqlCommand+=")";
            PreparedStatement ps = conn.prepareStatement(sqlCommand);          
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Answer a = new Answer();
                a.setId(rs.getInt(1));
                a.setQuestionId(rs.getInt(2));
                a.setContent(rs.getString(3));
                a.setIsTrue(rs.getBoolean(4));
                list.add(a);
            }
        } catch (SQLException ex) {
            logger.info(ex.getMessage());

        }
        return list;
}
    /**
     *
     * @param currentPage current page to select
     * @param pageSize number of records per page
     * @return list of answers
     */
    public ArrayList<Answer> getAnswers(int currentPage, int pageSize) {
        ArrayList<Answer> list = new ArrayList<Answer>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }
            QueryObject qo = new QueryObject("Answer", "id", "id");
            qo.setColumns("id,question_id, content, is_true");
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);

            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Answer a = new Answer();
                a.setId(rs.getInt(1));
                a.setQuestionId(rs.getInt(2));
                a.setContent(rs.getString(3));
                a.setIsTrue(rs.getBoolean(4));
                list.add(a);
            }

        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    public ArrayList<Answer> getAnswersOfQuestion(int questionId) {
        ArrayList<Answer> list = new ArrayList<Answer>();
        conn = DBUtil.getConnection();
        if (conn == null) {
            logger.info("Cannot get connection");
            return null;
        }
        try {
            PreparedStatement ps = conn.prepareStatement("select id,question_id, content, is_true from Answer where question_id=?");
            ps.setInt(1, questionId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Answer a = new Answer();
                a.setId(rs.getInt(1));
                a.setQuestionId(rs.getInt(2));
                a.setContent(rs.getString(3));
                a.setIsTrue(rs.getBoolean(4));
                list.add(a);
            }
        } catch (SQLException ex) {
            logger.info(ex.getMessage());

        }
        return list;
    }

    /**
     * add new answer
     *
     * @param a answer
     * @return greater than 0 value if the action is success, else return 0
     */
    public int create(Answer a) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlCreate);
            ps.setInt(1, a.getQuestionId());
            ps.setString(2, a.getContent());
            ps.setBoolean(3, a.getIsTrue());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * insert answers belong to a question
     * @param q - question (with 'id' fields)
     * @return number of effected rows. If action failed return 0
     */
    public int create(Question q) {
        int effectedRows = 0;
        String commandString = "insert into [answer](question_id,content, is_true) ";
        ArrayList<Answer> list = q.getAnswers();
        int size = list.size();
        for (int i = 0; i < size; i++) {
            if (i == 0) {
                commandString += " select ?, ?, ? ";
            } else if (i > 0) {
                commandString += " UNION ALL select ?, ?, ?";
            }
        }
        conn = DBUtil.getConnection();
        try {
            if (conn == null) {
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(commandString);
            for (int i = 0; i < size; i++) {
                ps.setInt(i * 3 + 1, q.getId());
                ps.setString(i * 3 + 2, list.get(i).getContent());
                ps.setBoolean(i * 3 + 3, list.get(i).getIsTrue());
            }
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info("Error: " + ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * update specified Answer
     *
     * @param a Answer to update
     * @return 1 or greater if action is successful, else return 0
     */
    public int update(Answer a) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlUpdate);
            ps.setInt(1, a.getQuestionId());
            ps.setString(2, a.getContent());
            ps.setBoolean(3, a.getIsTrue());
            ps.setInt(4, a.getId());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * update multi answer which belong to a question
     * @param q
     * @return 
     */
    public int update(Question q) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                return 0;
            }
            String sqlCommand = "update Answer set [Content]=newContent,is_true=newResult from (";

            int size = q.getAnswers().size();
            for (int i = 0; i < size; i++) {
                if (i == 0) {
                    sqlCommand += " select ? as 'id', ? as 'newContent', ? as 'newResult' ";
                } else {
                    sqlCommand += "union all select ?, ?, ? ";
                }
            }
            sqlCommand += ") tmp join answer on tmp.id=answer.id";
            PreparedStatement ps = conn.prepareStatement(sqlCommand);
            for(int i=0;i<size;i++){
                ps.setInt(i*3+1, q.getAnswers().get(i).getId());
                ps.setString(i*3+2, q.getAnswers().get(i).getContent());
                ps.setBoolean(i*3+3, q.getAnswers().get(i).getIsTrue());
            }
            effectedRows = ps.executeUpdate();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * delete a Answer by specifying identifier value
     *
     * @param id
     * @return 1 or greater value if action is successful , else return 0
     */
    public int delete(int id) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlDelete);
            ps.setInt(1, id);
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }
}
